﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
using MySql.Data.MySqlClient;
using eStock.Common;
using eStock.Properties;

namespace eStock.SQL
{
    class MySQL : SQLDatabase
    {
        public MySqlConnection Connection { get; private set; }

        public override bool Connect()
        {
            string connectionString = "SERVER=" + Settings.Default.db_host+ ";" +
                            "PORT=" + Settings.Default.db_port + ";" +
                            "DATABASE=" + Settings.Default.db_database + ";" +
                            "UID=" + Settings.Default.db_user + ";" +
                            "PASSWORD=" + Crypto.DecryptString(Settings.Default.db_pass) + ";";


            Connection = new MySqlConnection(connectionString);
            return true;
        }
        public override bool TryConnect(string server, string database, string user, string pass, int port = 3306)
        {
            string connectionString = "SERVER=" + server + ";" +
                            "DATABASE=" + port + ";" +
                            "DATABASE=" + database + ";" +
                            "UID=" + user + ";" +
                            "PASSWORD=" + pass + ";";


            Connection = new MySqlConnection(connectionString);
            try
            {
                Connection.Open();
                Connection.Close();
                return true;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                return false;
            }
        }

        public override bool Connect(string server, string database, string user, string pass, int port = 3306)
        {
            string connectionString = "SERVER=" + server + ";" +
                            "DATABASE=" + port + ";" +
                            "DATABASE=" + database + ";" +
                            "UID=" + user + ";" +
                            "PASSWORD=" + pass + ";";

            Connection = new MySqlConnection(connectionString);
            return true;
        }

        public override SQLDataReader QueryRead(string sqlcommand)
        {
            if (Connection == null)
                throw new SQLNotConnectedException();

            try
            {
                if(Connection.State != ConnectionState.Open)
                    Connection.Open();
                LastCommand = sqlcommand;
                MySqlCommand c = Connection.CreateCommand();
                c.CommandText = LastCommand;

                
                return new SQLDataReader(c.ExecuteReader());
            }
            catch (Exception x)
            {
                Connection.Close();
                LastError = new SQLError(x);
                throw LastError;
            }
        }
        public override long QueryWrite(string sqlcommand)
        {
            if (Connection == null)
                throw new SQLNotConnectedException();

            try
            {
                if (Connection.State != ConnectionState.Open)
                    Connection.Open();
                LastCommand = sqlcommand;
                MySqlCommand c = new MySqlCommand(LastCommand) { Connection = Connection };

                c.ExecuteNonQuery();
                return c.LastInsertedId;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }
        public override long QueryWrite(string sqlcommand, Dictionary<string, object> parameters)
        {
            if (Connection == null)
                throw new SQLNotConnectedException();

            try
            {
                Connection.Open();
                LastCommand = sqlcommand;
                MySqlCommand c = new MySqlCommand {Connection = Connection, CommandText = LastCommand};
                foreach (KeyValuePair<string, object> k in parameters)
                {
                    c.Parameters.AddWithValue(k.Key, k.Value);
                }
                c.ExecuteNonQuery();
                return c.LastInsertedId;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }


        public override Dictionary<string, object> FetchArray(SQLDataReader reader)
        {

            if (!reader.IsValid || !reader.Read())
            {
                Connection.Close();
                return null;
            }

            try
            {
                Dictionary<string, object> res = new Dictionary<string, object>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    res.Add(reader.GetName(i), reader.GetValue(i));
                }
                return res;
            }
            catch (Exception x)
            {
                Connection.Close();
                LastError = new SQLError(x);
                throw LastError;
            }
        }


        public override int FetchCount(SQLDataReader reader)
        {
            try
            {
                int i = 0;
                while (reader.Read()) i++;
                return i;
            }
            catch (Exception x)
            {
                LastError = new SQLError(x);
                throw LastError;
            }
            finally
            {
                Connection.Close();
            }
        }

        public override void Disconnect()
        {
            if (Connection != null)
                Connection.Close();
        }

        public override string DateFormat(string r, string f)
        {
            return "DATE_FORMAT("+ r + ", '" + f + "')";
        }
    }
}
